\documentclass[12pt]{report}
\usepackage{geometry}                % See geometry.pdf to learn the layout options. There are lots.
\geometry{letterpaper}                   % ... or a4paper or a5paper or ... 
%\geometry{landscape}                % Activate for for rotated page geometry
\usepackage[parfill]{parskip}    % Activate to begin paragraphs with an empty line rather than an indent
\usepackage{graphicx}
\usepackage{amssymb}
\usepackage{epstopdf}
\DeclareGraphicsRule{.tif}{png}{.png}{`convert #1 `dirname #1`/`basename #1 .tif`.png}
\newcommand{\HRule}{\rule{\linewidth}{0.5mm}}
\begin{document}
\begin{titlepage}

\begin{center}


\textsc{\LARGE University of Alberta}\\[1.5cm]

\textsc{\Large Term Project}\\[0.5cm]


% Title
\HRule \\[0.4cm]
{ \huge \bfseries RaySyS}\\[0.4cm]

\HRule \\[1.5cm]

% Author and supervisor
\begin{minipage}{0.4\textwidth}
\begin{flushleft} \large
Steven  \textsc{Maschmeyer}\\
Camden  \textsc{Narzt}\\
Oscar  \textsc{Ramirez}
\end{flushleft}
\end{minipage}
\begin{minipage}{0.4\textwidth}
\begin{flushright} \large
\emph{Professor:} \\
Dr. Li-Yan \textsc{Yuan}
\end{flushright}


\end{minipage}

\vfill

% Bottom of the page
{\large \today}

\end{center}

\end{titlepage}
\chapter*{RaySys}
RaySys consists of several modules that allow different types of users to manage and interact a Radiological Database System. With this application users are divided into four different categories: administrators, patients, doctors, and radiologists each having different privileges. In order to summarize the system architecture and the major modules used in the system, along with their relationships and main interactions with the database this document will first describe those sections that are common to all users, and then it will be divided into specific user sections. Each section will contain a listing of the relevant \emph{*.jsp} and \emph{*.java} files for the module, along with the initial \emph{URL} which can be used to access it.

\section*{Login}

The login module is the first place the user will interact with RaySys. The user will first encounter this when accessing the system for the first time. At this point a login screen requesting a \texttt{username} and a \texttt{password} is shown. If the user enters information for a valid account a \emph{session variable} \texttt{userName} will be set be a String representing the user's account type. Also as expected if the information is invalid the user will be notified appropriately. For added security sessions are set to expire after $5$ minutes, therefore if the user is inactive for a period of time any action will redirect the user to the login screen.

The action of logging in utilizes a the servlet \texttt{LoginManager.java} which is located in the \texttt{loginModule} package. Through this server a prepared statement is created with the form \texttt{select password, class from users where user\_name = ?} and the name is set to equal the username provided. The result set that is generated from executing this query is then inspected and if a password was returned it is compared to the provided one by the user.

\begin{quote}
\emph{URL:} \texttt{/radiologydb/login.jsp} \\
\emph{*.jsp: } \texttt{login.jsp} \\
\emph{*.java: } \texttt{LoginManager.java}
\end{quote}

\section*{Profile}
Another part of the login module is the profile. This provides an interface for the user to update most of his information. All users are allowed to change: \emph{first name, last name, password, address, email,} and \emph{ phone}. This section of the login module takes advantage of a servlet that is also used for updating user information by admins so the specifications of the servlet used can be found in the \emph{User Update} section.

\begin{quote}
\emph{URL:} \texttt{/radiologydb/profile.jsp} \\
\emph{*.jsp: } \texttt{profile.jsp} \\
\emph{*.java: } \texttt{UserUpdate.java}\\
\emph{Note:} Further information found in \emph{User Update} section
\end{quote}

\section*{Connections}
Connections to the database are managed by \texttt{ConnectionManager}, which provide a static methods for opening pooled connections from an \texttt{OracleConnectionPoolDataSource}. (Note that since the server loads outdated JDBC drivers from \texttt{classes12.jar}, the connection cache was implemented using the deprecated class \texttt{OracleConnectionCacheImpl} instead of using the implicit connection caching mechanism provided by \texttt{OracleConnectionCacheManager}.)

\texttt{ConnectionManager} also provides a nested class \texttt{ConnectionKit} to help ease the execution of queries, and to ensure that resources are properly closed.

\begin{quote}
\emph{*.java: } \texttt{ConnectionManager.java}\\
\end{quote}
\section*{Search Records}
The home page of RaySys is the Search Records page, which allows users to find records over the columns \emph{patient\_name}, \emph{diagnosis}, and or \emph{description} defined in the \emph{radiology\_record} table. The user may specify start or end dates to limit the range of the search. By default, the results are returned by decreasing rank, where the rank of a given result record is defined as \emph{6*frequency(patient\_name) + 3*frequency(diagnosis) + frequency(description)}. Alternatively, the user can use the 'Order By' combo box to order the results by increasing or decreasing \emph{test\_date}. The resulting records are displayed in a table, with the list of thumbnails dispalyed in a \texttt{ContentFlow} (see 3rd party libraries). The user can scroll through the images in the \texttt{ContentFlow} and then click on one to see the full size image in a separate browser. Record search is available to all users, however the results are filtered depending on the user class (defined in the session).

Records searches are initiated by \emph{/search.jsp} which creates a new \texttt{RecordsQuery} to get a connection and carry out the actual query. \emph{search.jsp} passes in relevant parameters -- such as the search input -- and \texttt{RecordsQuery} wraps the resulting \texttt{ResultSet} to reduce the amount of back-end work done by the \emph{jsp} page. The exact query that is executed depends on the arguments that are passed to \texttt{RecordsQuery}. In particualar, the query will change slightly depending on how the user chooses to order the results, or if the user has selected to limit the results to a particular range of dates.

The resulting query has the following form, where square brackets denote segments that change depending on user input:

\texttt{select (6*score(1) + 3*score(2) + score(3)) as rank, record\_id, \\patient\_name,doctor\_name, radiologist\_name, test\_type, \\prescribing\_date, test\_date, diagnosis, description \\ \\from radiology\_record where (contains(patient\_name, [KEYWORDS], 1) > 0 or contains(diagnosis, [KEYWORDS], 2) > 0 or contains(description, [KEYWORDS], 3) > 0)}\\

$if user class is patient$ \texttt{and patient\_name = [USERNAME]}\\
 
$if user class is radiologist$ \texttt{and radiologist\_name = [USERNAME]}\\
  
$if user class is doctor$ \texttt{AND (doctor\_name = [USER\_NAME] OR patient\_name in\\ (select patient\_name from family\_doctor where doctor\_name = [USERNAME]))}
   
   \texttt{     
        [and test\_date >= to\_date([STARTDATE], 'dd/MM/yyyy')]
        [and test\_date <= to\_date([ENDDATE], 'dd/MM/yyyy')]
	[order by test\_date asc]
	[order by test\_date desc]
	[order by myscore desc]}

The query is executed in a prepared statement to help protect against SQL injection.

\texttt{RecordsQuery} creates a PicsQuery for each record to fetch the image URLs for that record. The image URLs are generated by the \texttt{GetOnePic} servlet as demonstrated in the example files on the project page. The exact query executed for each record, image, and style is: 

 \texttt{select [STYLE] from pacs\_images where record\_id = [RECORDID] and image\_id = [IMAGEID]}.


\begin{quote}
\emph{URL:} \texttt{/radiologydb/index.jsp} \\
\emph{*.jsp: } \texttt{index.jsp} \\
\end{quote}

\section*{Adding Users}
Adding users is only available to admins and the interface is very simple. Admins can add users by only setting the \emph{username, password,} and \emph{class}. This will add an entry to the \texttt{users} table in the database by utilizing the prepared statement \texttt{insert into users values (?,?,?,?)} where the values correspond to the ones listed before hand, and the fourth value is set to the registration time which is generated when the user is added.

If the administrator decides to fill the remaining fields \emph{first name, last name, address, email, phone} an entry will be created in the persons entry with the supplied information.

\begin{quote}
\emph{URL:} \texttt{/radiologydb/new-uesr.jsp} \\
\emph{*.jsp: } \texttt{new-user.jsp} \\
\emph{*.java: } \texttt{NewUser.java}
\end{quote}

\section*{User Update}
The user update module's main purpose is to be utilized by administrators. This module provides an interface for administrators to update all information for any user type. The interface for this begins by providing the administrator a search for users where he can search by username, name, or last name and can then select what user to edit from the displayed results.

The interface for changing basic information is very similar to the one used to add users so no explanation should be needed. If the user to be modified is a patient or a doctor this module will display a list of all the doctors or patients available for choosing and a second list with the ones that are currently the patient/doctor's patients/doctors.

In order to edit user information two prepared statements, similar to those for adding new users, are used. The first prepared stament used is \texttt{update users set password=?, class = cast(? as char(1)) where user\_name =?} and \texttt{update persons set first\_ name =?, last\_name=?, address=?, email=?, phone =? where user\_name=?}.

In order to display the available doctors the statement \texttt{select UNIQUE patient\_name from family\_doctor where doctor\_name = '" + username + "'} is used. In order to make a change to the \emph{family\_doctor} table we will either add a new row with \texttt{insert into family\_doctor values('" + username + "', '" + uname + "')} or remove a row with \texttt{delete from family\_doctor where doctor\_name = '" + username + "' and patient\_name = '" + uname + "'}. Very similar queries are used for getting, adding and deleting patient rows.

\begin{quote}
\emph{URL:} \texttt{/radiologydb/update/index.jsp} \\
\emph{*.jsp: } \texttt{/update/index.jsp /index/user-search.jsp /index/update.jsp} \\
\emph{*.java: } \texttt{UserUpdate.java PersonSearch.java}
\end{quote}

\section*{Report Generation}
Report generation presents the user with the now very familiar search layout. This search is available to administrators and it allows them to search for a specific diagnosis in a time range. The results will then display the user information for those with the searched diagnosis along with the date of the first diagnosis for the searched type.

\begin{quote}
\emph{URL:} \texttt{/radiologydb/report/index.jsp} \\
\emph{*.jsp: } \texttt{/report/index.jsp /report/search.jsp} \\
\emph{*.java: } \texttt{DiagnosisSearch.java}
\end{quote}

\section*{Record Creation}
The record creation is done in two parts, first a simple form that takes in all relevant data for a Radiology Record, then a servlet to send that data to oracle using the sql statements: texttt{"SELECT rec\_id\_sequence.nextval from dual"} and  \texttt{"insert into radiology\_record(record\_ id,patient\_name,doctor\_name, radiologist\_name , test\_type,prescribing\_date,test\_date,diagnosis,description) values (?,?, ?, ?,?, ?, ? ,?,?)"} where the '?''s are replaced with the relevant data using \emph{setOBJTYPE()} style methods.
The status of the creation is flashed to the screen after the user submits and upon success the page forwards to an image upload page with the record id already filled in, and upon failure return the user to the record create page, data intact to try and fix the data or figure out their error.  

\begin{quote}
\emph{*.jsp:} \texttt{/radiologydb/create-record.jsp} \\
\emph{*.java: } \texttt{CreateRecord.java}
\end{quote}



\section*{Image Upload}
The image uploading is done in two parts the image path on the users computer and record id are chosen on one page, and then upon submit the data is passed to a servlet and the image is read in and resized to the three relevant sizes, then stored in the database using the SQL statements:\\
        
        \texttt{ResultSet rset1 = stmt.executeQuery("SELECT pic\_id\_sequence.nextval\\from dual");}\\
        
        \texttt{stmt.execute( "insert into pacs\_images (record\_id,image\_id,thumbnail,\\regular\_size, full\_size) values ("+record\_id+","+pic\_id+",\\empty\_blob(),empty\_blob(),empty\_blob())");}\\

again if the upload is successful the page forwards you to the upload page with a success message, otherwise it flashes an error message and returns you to the upload page to try and fix the data.

\section*{Further Work}
The current implementation of RaySys is a great starting point for any type of medical database system where multiple types of user must be supported. In order to improve upon this implementation several key points would have to be polished. The first thing that would need some work would be to standardize the use of ConnectionKit amongst all modules. This would provide a very cohesive and centralized interface for managing the connections and would provide a very easy way to upgrade when the database drivers are updated.

Another element that could benefit the user greatly would be support for multiple image uploads. This would simplify and speed up the process of inserting long records into the database. By handling multiple image uploads at the same time several optimizations could be made to make the process faster.

\section*{Acknowledgements}

Calendar Popup http://www.mattkruse.com/javascript/calendarpopup/index.html

ContentFlow  http://www.jacksasylum.eu/ContentFlow/

Selection Box http://www.web-savant.com/users/kathi/asp

\end{document}  